﻿'Copyright ?1995-2007, Camstar Systems, Inc. All Rights Reserved.
'Description:返工返修数据访问
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:tianFW
'create Date:2017-5-25
Imports BusinessRule.IBusiness
Imports DatabaseAccess.DBUtility
Imports System.Data.OracleClient
Imports System.Text
Imports BusinessRule.DTO

Public Class uLMSReWorkBusiness
    Implements uLMSReWorkIBusiness
    ''' <summary>
    ''' 获取明细表中相关编号最大返修编号
    ''' </summary>
    ''' <param name="strValue"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMaxStepReworkInfoNO(ByVal strValue As String) As DataTable Implements IBusiness.uLMSReWorkIBusiness.GetMaxStepReworkInfoNO
        Dim strsql As String = String.Format("SELECT MAX(STEPREWORKINFONO) AS STEPREWORKINFONO FROM STEPREWORKINFO S WHERE S.STEPREWORKINFONO LIKE '{0}%'", strValue)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        Return dt
    End Function

    ''' <summary>
    ''' 添加返工返修信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function AddStepReworkInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSReWorkIBusiness.AddStepReworkInfo
        AddStepReworkInfo = False
        Dim strsql As String =
            <string>
                INSERT INTO STEPREWORKINFO(CHANGEBILLNO,CHECKEMPLOYEE,CHECKWORKCENTER,CONTAINERID,CONTAINERNAME,CREATEDATE,PRODUCTID,RESPONSEWORKCENTER,RESPONSEWORKER,
                STEPREWORKINFOID,STEPREWORKINFONO,FACTORYID,ISTRANSFER,WORKFLOWSTEPID) VALUES(:CHANGEBILLNO,:CHECKEMPLOYEE,:CHECKWORKCENTER,:CONTAINERID,:CONTAINERNAME,SYSDATE,:PRODUCTID,
                :RESPONSEWORKCENTER,:RESPONSEWORKER,:STEPREWORKINFOID,:STEPREWORKINFONO,:FACTORYID,0,:WORKFLOWSTEPID)
            </string>
        Dim insertpara(11) As OracleParameter
        insertpara(0) = DatabaseHelper.NewParameter(":CHANGEBILLNO", para("ChangeBillNo"), OracleType.VarChar)
        insertpara(1) = DatabaseHelper.NewParameter(":CHECKEMPLOYEE", para("CheckEmployee"), OracleType.VarChar)
        insertpara(2) = DatabaseHelper.NewParameter(":CHECKWORKCENTER", para("CheckWorkCenter"), OracleType.VarChar)
        insertpara(3) = DatabaseHelper.NewParameter(":CONTAINERID", para("ContainerID"), OracleType.VarChar)
        insertpara(4) = DatabaseHelper.NewParameter(":CONTAINERNAME", para("ContainerName"), OracleType.VarChar)
        insertpara(5) = DatabaseHelper.NewParameter(":PRODUCTID", para("ProductID"), OracleType.VarChar)
        insertpara(6) = DatabaseHelper.NewParameter(":RESPONSEWORKCENTER", para("ResponseWorkCenter"), OracleType.VarChar)
        insertpara(7) = DatabaseHelper.NewParameter(":RESPONSEWORKER", para("ResponseWorker"), OracleType.VarChar)
        insertpara(8) = DatabaseHelper.NewParameter(":STEPREWORKINFOID", para("StepReworkInfoID"), OracleType.VarChar)
        insertpara(9) = DatabaseHelper.NewParameter(":STEPREWORKINFONO", para("StepReworkInfoNO"), OracleType.VarChar)
        insertpara(10) = DatabaseHelper.NewParameter(":FACTORYID", para("FactoryID"), OracleType.VarChar)
        insertpara(11) = DatabaseHelper.NewParameter(":WORKFLOWSTEPID", para("WorkflowStepID"), OracleType.VarChar)
        Dim row As Integer = DatabaseHelper.ExecuteSql(strsql, insertpara)
        If row > 0 Then
            AddStepReworkInfo = True
        End If
    End Function

    ''' <summary>
    ''' 更新返工返修信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateReworkInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSReWorkIBusiness.UpdateReworkInfo
        UpdateReworkInfo = False
        Dim strsql As String =
            <string>
                UPDATE STEPREWORKINFO SET CHANGEBILLNO=:CHANGEBILLNO,RESPONSEWORKCENTER=:RESPONSEWORKCENTER,RESPONSEWORKER=:RESPONSEWORKER,ISTRANSFER=0 WHERE STEPREWORKINFOID=:STEPREWORKINFOID
            </string>
        Dim insertpara(3) As OracleParameter
        insertpara(0) = DatabaseHelper.NewParameter(":CHANGEBILLNO", para("ChangeBillNo"), OracleType.VarChar)
        insertpara(1) = DatabaseHelper.NewParameter(":RESPONSEWORKCENTER", para("ResponseWorkCenter"), OracleType.VarChar)
        insertpara(2) = DatabaseHelper.NewParameter(":RESPONSEWORKER", para("ResponseWorker"), OracleType.VarChar)
        insertpara(3) = DatabaseHelper.NewParameter(":STEPREWORKINFOID", para("StepReworkInfoID"), OracleType.VarChar)
        Dim row As Integer = DatabaseHelper.ExecuteSql(strsql, insertpara)
        If row > 0 Then
            UpdateReworkInfo = True
        End If
    End Function

    ''' <summary>
    ''' 添加返修信息明细
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="stepreworkid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function AddStepReworkInfoDetail(ByVal dt As DataTable, ByVal stepreworkid As String) As Boolean Implements IBusiness.uLMSReWorkIBusiness.AddStepReworkInfoDetail
        AddStepReworkInfoDetail = False
        Dim strsql As New StringBuilder()
        For Each row As DataRow In dt.Rows
            strsql.AppendLine("INSERT INTO STEPREWORKINFODETAIL(STEPREWORKINFOID, QTY, LOSSREASONID) VALUES(:STEPREWORKINFOID,:QTY,:LOSSREASONID)")
            Dim insertpara(2) As OracleParameter
            insertpara(0) = DatabaseHelper.NewParameter("STEPREWORKINFOID", stepreworkid, OracleType.VarChar)
            insertpara(1) = DatabaseHelper.NewParameter("QTY", row("ReWorkQty"), OracleType.VarChar)
            insertpara(2) = DatabaseHelper.NewParameter("LOSSREASONID", row("ReWorkReason"), OracleType.VarChar)
            Dim r As Integer = DatabaseHelper.ExecuteSql(strsql.ToString, insertpara)
            If r > 0 Then
                AddStepReworkInfoDetail = True
                strsql.Clear()
            End If
        Next
    End Function

    ''' <summary>
    ''' 按唯一id删除返工返修明细
    ''' </summary>
    ''' <param name="id"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DelStepReworkInfoDetail(ByVal id As String) As Boolean Implements IBusiness.uLMSReWorkIBusiness.DelStepReworkInfoDetail
        DelStepReworkInfoDetail = False
        Dim strsql As String = String.Format("DELETE FROM STEPREWORKINFODETAIL WHERE STEPREWORKINFOID='{0}'", id)
        Dim row As Integer = DatabaseHelper.ExecuteSql(strsql)
        If row > 0 Then
            DelStepReworkInfoDetail = True
        End If
    End Function

    ''' <summary>
    ''' 查看是否已存在返修信息
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function IsExistStepReworkInfo(ByVal strContainerID As String) As DataTable Implements IBusiness.uLMSReWorkIBusiness.IsExistStepReworkInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT S.STEPREWORKINFOID, S.RESPONSEWORKCENTER,S.RESPONSEWORKER,S.CHANGEBILLNO,S.STEPREWORKINFONO,S.CHECKWORKCENTER,S.CHECKEMPLOYEE  ")
        strsql.AppendLine(" FROM STEPREWORKINFO S LEFT JOIN CONTAINERCURRENTINFO C ON C.CONTAINERID = S.CONTAINERID")
        strsql.AppendFormat(" WHERE C.WORKFLOWSTEPID= S.WORKFLOWSTEPID AND S.CONTAINERID='{0}'", strContainerID)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' 查询已存在的返修信息明细表
    ''' </summary>
    ''' <param name="strStepReworkInfoID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function FindIsExistStepReworkInfoDetail(ByVal strStepReworkInfoID As String) As DataTable Implements IBusiness.uLMSReWorkIBusiness.FindIsExistStepReworkInfoDetail
        Dim strsql As String = String.Format("SELECT * FROM STEPREWORKINFODETAIL S WHERE S.STEPREWORKINFOID='{0}'", strStepReworkInfoID)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        Return dt
    End Function

    ''' <summary>
    ''' 获取所的有返工返修的批次信息 
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetReWorkContainerInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSReWorkIBusiness.GetReWorkContainerInfo
        Dim strSelect As String =
           <string>
                SELECT SRI.FACTORYID,PB.PRODUCTNAME,P.DESCRIPTION PRODUCTDESCRIPTION,C_.TOGETHERFLAG,P.PRODUCTID,C_.CONTAINERTYPE,C_.PLANNEDCOMPLETIONDATE,C_.PLANNEDSTARTDATE,
                DECODE(C_.CONTAINERTYPE,2,'批量加工',3,'批量带子序号',7,'单件加工') AS CONTAINERTYPENAME,MF.MFGORDERNAME,WS.WORKFLOWSTEPID,WS.WORKFLOWSTEPNAME
            </string>
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT * FROM(")
        strsql.AppendLine(strSelect)
        strsql.AppendLine(",SUM(C_.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,'' FaContianerID ")
        Dim selectStr As String =
            <string>
                FROM CONTAINER C_ LEFT JOIN PRODUCT P ON P.PRODUCTID = C_.PRODUCTID
	            LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = P.PRODUCTBASEID
	            LEFT JOIN MFGORDER MF ON MF.MFGORDERID = C_.MFGORDERID
	            LEFT JOIN STEPREWORKINFO SRI ON SRI.CONTAINERID = C_.CONTAINERID
	            LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = SRI.WORKFLOWSTEPID
            </string>
        strsql.AppendLine(selectStr)
        Dim strcondition As New StringBuilder
        strcondition.AppendFormat("WHERE SRI.STEPREWORKINFOID IS NOT NULL AND SRI.FACTORYID='{0}'", para("FactoryID"))
        If (para.Keys.Contains("ScanContainerName")) Then
            strcondition.AppendFormat(" AND C_.CONTAINERNAME='{0}'", para("ScanContainerName"))
        Else
            If (para.Keys.Contains("ContainerName")) Then
                strcondition.AppendFormat(" AND C_.CONTAINERNAME LIKE '%{0}%'", para("ContainerName"))
            End If
            If (para.Keys.Contains("ProductName")) Then
                strcondition.AppendFormat(" AND PB.PRODUCTNAME LIKE '%{0}%'", para("ProductName"))
            End If
            If para.Keys.Contains("mfgOrderName") Then
                strcondition.AppendFormat(" AND MF.MFGORDERNAME  LIKE '%{0}%'", para("mfgOrderName"))
            End If
        End If
        strsql.AppendLine(strcondition.ToString())
        strsql.AppendLine("AND C_.CONTAINERTYPE='7'")
        strsql.AppendLine(" GROUP BY SRI.FACTORYID,PB.PRODUCTNAME,P.DESCRIPTION,C_.TOGETHERFLAG,P.PRODUCTID,C_.CONTAINERTYPE,C_.PLANNEDCOMPLETIONDATE,C_.PLANNEDSTARTDATE,")
        strsql.AppendLine("C_.CONTAINERID,MF.MFGORDERNAME,WS.WORKFLOWSTEPID,WS.WORKFLOWSTEPNAME")
        strsql.AppendLine(" UNION ")
        strsql.AppendLine(strSelect)
        strsql.AppendLine(",C_.QTY AS WIPQTY,C_.CONTAINERID,C_.CONTAINERNAME,C_.CONTAINERID AS FaContianerID")
        strsql.AppendLine(selectStr)
        strsql.AppendLine(strcondition.ToString())
        strsql.AppendLine("AND C_.CONTAINERTYPE<>'7'")
        strsql.AppendLine(") C ")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strsql.ToString
        Dim strValue As String = ""
        strValue += "SELECT * FROM("
        strValue += strSelect
        strValue += ",C_.QTY,C_.CONTAINERID,C_.CONTAINERNAME"
        strValue += selectStr
        strValue += strcondition.ToString()
        strValue += ") C WHERE 1=1 "
        Return DatabaseHelper.GetContainerForPage(paraPage, strValue)
    End Function

    ''' <summary>
    ''' 根据选择的批次信息获取该批次的所有返工返修信息
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetStepReworkInfoByContainerID(ByVal containerID As String, ByVal WorkflowStepID As String) As DataTable Implements IBusiness.uLMSReWorkIBusiness.GetStepReworkInfoByContainerID
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT PB.PRODUCTNAME, P.DESCRIPTION, SWI.STEPREWORKINFONO, C.CONTAINERNAME,SWI.CREATEDATE,")
        strsql.AppendLine("SWI.RESPONSEWORKCENTER, SWI.CHANGEBILLNO, SWI.RESPONSEWORKER, SWI.CHECKEMPLOYEE CHECKER,SWI.STEPREWORKINFOID,SWI.CHECKWORKCENTER")
        strsql.AppendLine("FROM STEPREWORKINFO SWI LEFT JOIN PRODUCT P ON SWI.PRODUCTID = P.PRODUCTID")
        strsql.AppendLine("LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = P.PRODUCTBASEID")
        strsql.AppendLine("LEFT JOIN CONTAINER C ON C.CONTAINERID = SWI.CONTAINERID")
        strsql.AppendFormat("WHERE ({0}) AND SWI.WORKFLOWSTEPID='{1}' AND C.STATUS=1 ORDER BY SWI.CREATEDATE DESC", containerID, WorkflowStepID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
    End Function

    ''' <summary>
    ''' 获取总的返工返修批次数
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCountofStepReworkInfo(ByVal para As Dictionary(Of String, String)) As Integer Implements IBusiness.uLMSReWorkIBusiness.GetCountofStepReworkInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT MAX(RN) MAX_RN FROM (SELECT ROWNUM RN")
        strsql.AppendLine("FROM (SELECT C.CONTAINERID FROM CONTAINER C LEFT JOIN PRODUCT P ON P.PRODUCTID = C.PRODUCTID")
        strsql.AppendLine("LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = P.PRODUCTBASEID")
        strsql.AppendLine("LEFT JOIN MFGORDER MF ON MF.MFGORDERID = C.MFGORDERID")
        strsql.AppendLine("INNER JOIN (SELECT DISTINCT (CC.CONTAINERID) FROM CONTAINER CC")
        strsql.AppendLine("LEFT JOIN STEPREWORKINFO SRI ON SRI.CONTAINERID = CC.CONTAINERID")
        strsql.AppendLine("WHERE SRI.STEPREWORKINFOID IS NOT NULL")
        strsql.AppendLine(") K ON K.CONTAINERID = C.CONTAINERID")
        If para.Keys.Contains("ScanContainerName") Then
            strsql.AppendFormat(" WHERE CONTAINERNAME='{0}'", para("ScanContainerName"))
        Else
            strsql.AppendLine(" WHERE 1=1 ")
            If para.Keys.Contains("ContainerName") Then
                strsql.AppendFormat(" AND C.CONTAINERNAME like '%{0}%'", para("ContainerName"))
            End If
            If (para.Keys.Contains("ProductName")) Then
                strsql.AppendFormat(" AND PB.PRODUCTNAME like '%{0}%'", para("ProductName"))
            End If
            If para.Keys.Contains("mfgOrderName") Then
                strsql.AppendFormat(" AND MF.MFGORDERNAME  LIKE '%{0}%'", para("mfgOrderName"))
            End If
        End If
        strsql.AppendLine(" ) A)")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
        If IsDBNull(dt.Rows(0)("MAX_RN")) Then
            Return 0
        End If
        Return dt.Rows(0)("MAX_RN")
    End Function

    ''' <summary>
    ''' 根据返修返工ID获取返工返修详细信息
    ''' </summary>
    ''' <param name="stepreworkinfoID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetStepReworkInfodetial(ByVal stepreworkinfoID As String) As DataTable Implements IBusiness.uLMSReWorkIBusiness.GetStepReworkInfodetial
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT SWD.*,LA.LOSSREASONNAME FROM STEPREWORKINFODETAIL SWD ")
        strsql.AppendLine(" LEFT JOIN LOSSREASON LA ON LA.LOSSREASONID = SWD.LOSSREASONID")
        strsql.AppendFormat(" WHERE {0}", stepreworkinfoID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
    End Function

    ''' <summary>
    ''' 获取返工返修信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetreWorkInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSReWorkIBusiness.GetreWorkInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine(" SELECT S.*, A.REWORKQTY FROM STEPREWORKINFO S")
        strsql.AppendLine("LEFT JOIN (SELECT SUM(SD.QTY) REWORKQTY, SD.STEPREWORKINFOID")
        strsql.AppendLine("FROM STEPREWORKINFODETAIL SD GROUP BY SD.STEPREWORKINFOID) A")
        strsql.AppendLine("ON A.STEPREWORKINFOID = S.STEPREWORKINFOID")
        strsql.AppendFormat(" WHERE S.CONTAINERID='{0}' AND S.WORKFLOWSTEPID='{1}' ORDER BY S.CREATEDATE DESC", para("ContainerID"), para("WorkflowStepID"))
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 获取所有符合条件的批次当前序返工记录
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCurrentReWorkInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSReWorkIBusiness.GetCurrentReWorkInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("select PRODUCTDESCRIPTION,PRODUCTNAME,PRODUCTID,CONTAINERNO,WORKFLOWSTEPID,WORKFLOWSTEPNAME,")
        strsql.AppendLine("WORKFLOWSTEPSEQUENCE,sum(QTY) QTY,sum(REWORKQTY) REWORKQTY,CHECKEMPLOYEE,CREATEDATE from(")
        strsql.AppendLine("SELECT CU.PRODUCTDESCRIPTION,CU.PRODUCTNAME,CU.PRODUCTID,CU.CONTAINERNO,CU.WORKFLOWSTEPID,")
        strsql.AppendLine("CU.WORKFLOWSTEPNAME,CU.WORKFLOWSTEPSEQUENCE,CU.QTY,A.REWORKQTY,CRI.CHECKEMPLOYEE,")
        strsql.AppendLine("to_char(CRI.CREATEDATE,'yyyy/MM/dd hh24:Mi') CREATEDATE FROM CONTAINERCURRENTINFO CU")
        strsql.AppendLine("INNER JOIN STEPREWORKINFO CRI ON CU.CONTAINERID = CRI.CONTAINERID AND CU.WORKFLOWSTEPID = CRI.WORKFLOWSTEPID")
        strsql.AppendLine("LEFT JOIN (SELECT SUM(CRID.QTY) REWORKQTY,CRID.STEPREWORKINFOID FROM STEPREWORKINFODETAIL CRID GROUP BY CRID.STEPREWORKINFOID) A ")
        strsql.AppendLine("ON A.STEPREWORKINFOID = CRI.STEPREWORKINFOID")
        strsql.AppendFormat(" WHERE CRI.FACTORYID='{0}' ORDER BY CRI.CREATEDATE DESC", para("FactoryID"))
        strsql.AppendLine(") group by PRODUCTDESCRIPTION,PRODUCTNAME,PRODUCTID,CONTAINERNO,WORKFLOWSTEPID,WORKFLOWSTEPNAME,")
        strsql.AppendLine("WORKFLOWSTEPSEQUENCE,CHECKEMPLOYEE,CREATEDATE")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 更新服务总表批次的返工返修标识
    ''' </summary>
    ''' <param name="containerID">批次ID</param>
    ''' <param name="type">1 当前序有返工返修记录标识 0 无</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateReWorkNum(ByVal containerID As String, ByVal type As Integer) As Boolean Implements uLMSReWorkIBusiness.UpdateReWorkNum
        UpdateReWorkNum = False
        Dim strsql As String = String.Format("UPDATE CONTAINERCURRENTINFO CU SET CU.Isrework = {0} WHERE CU.CONTAINERID = '{1}'", type, containerID)
        If Not DatabaseHelper.ExecuteSql(strsql) > 0 Then
            Exit Function
        End If
        UpdateReWorkNum = True
    End Function
End Class
